package jt.classic.system.tablequery;

import jt.classic.system.ISystem;
import jt.classic.system.role.IRole;
import jt.classic.system.user.IUser;
import jt.classic.system.wordbook.XWordbook;
import jt.classic.system.wordbook.XWordbookMain;
import jt.classic.system.wordbook.XWordbookQuery;

import org.limp.basework.Column;
import org.limp.basework.Table;

import org.limp.mine.StringTool;

import java.util.List;
import java.util.Map;


/**
 * 对象实体数据集合查询工具类
 *
 * @author mido
 *
 */
public final class TableARGTool {
    /**
     * 对象数据列表查询 count sql 编写
     * @param query
     * @param param
     * @return String
     */
    public static String getListSQL4Query(IUser user, TableQuery tq, Map param) {
        StringBuffer tableStr = new StringBuffer();

        List tables = tq.itables();

        Table primaryTable = (Table) tables.get(0);

        tableStr.append(primaryTable.getName());

        if (tables.size() > 1) {
            for (int i = 1; i < tables.size(); i++) {
                Table table = (Table) tables.get(i);
                tableStr.append("," + table.getName());
            }
        }

        StringBuffer sql = new StringBuffer("SELECT ");

        if (primaryTable.hasPrimaryKey()) {
            try {
                Column column = primaryTable.column(primaryTable.getPrimaryKey()[0]);

                sql.append(primaryTable.getName() + "." + column.getName());
                sql.append(",");
            } catch (Exception ex) {
                ISystem.catchException(ex);
            }
        }

        List resultItems = tq.iresultItems();

        if ((resultItems != null) && !resultItems.isEmpty()) {
            for (int i = 0; i < resultItems.size(); i++) {
                Map item = (Map) resultItems.get(i);

                String alien = (String) item.get("alien");

                if (!alien.equals("1")) {
                    Column column = (Column) item.get("column");

                    if (!column.isAnnex()) {
                        sql.append(column.associateTable().getName() + "." +
                            column.getName());

                        sql.append(",");
                    }
                }
            }

            /*去掉多余的","*/
            sql = new StringBuffer(sql.substring(0, sql.length() - 1));
        }

        sql.append(" FROM " + tableStr.toString());

        /*角色权限校验*/
        if (!tq.ipurview().equals("")) {
            if (!primaryTable.hasPrimaryKey()) {
                System.err.println("table ['" + primaryTable.getName() +
                    "'] has no primary, cant't list with purivew");
            } else {
                String purviewSql = getPurviewSql(user, tq, primaryTable);

                sql.append(",(" + purviewSql + ") A");
            }
        }

        /*表关系项定义*/
        List relateItems = tq.irelateItems();

        /*查询项定义*/
        List queryItems = tq.iqueryItems();

        XWordbookMain wbmain = XWordbookMain.getInstance();

        XWordbookQuery wbquery = wbmain.getWordbookQuery("compare-mode");

        sql.append(" WHERE 1 = 1 ");

        if ((tables.size() > 1) &&
                ((relateItems != null) && !relateItems.isEmpty())) {
            StringBuffer relateStr = new StringBuffer();

            for (int i = 0; i < relateItems.size(); i++) {
                Map item = (Map) relateItems.get(i);

                Column leftRelateColumn = (Column) item.get("leftRelateColumn");

                Column rightRelateColumn = (Column) item.get(
                        "rightRelateColumn");

                relateStr.append(" AND " +
                    leftRelateColumn.associateTable().getName() + "." +
                    leftRelateColumn.getName() + " = " +
                    rightRelateColumn.associateTable().getName() + "." +
                    rightRelateColumn.getName());
            }

            sql.append(relateStr);
        }

        if ((queryItems != null) && !queryItems.isEmpty()) {
            for (int i = 0; i < queryItems.size(); i++) {
                Map item = (Map) queryItems.get(i);

                String defaultValue = StringTool.checkString(item.get(""));

                String cvalue = StringTool.checkString(param.get(
                            ((Column) item.get("column")).getName()), "");

                //param.put(item.icolumn().getName(), cvalue);
                int paramType = 0;

                /*checkbox*/
                if (item.get("type").equals("checkbox") && !cvalue.equals("")) {
                    if (cvalue.indexOf(",") > -1) {
                        paramType = 2;
                    } else {
                        paramType = 1;
                    }
                } else if (!item.get("type").equals("checkbox") &&
                        !cvalue.equals("")) {
                    paramType = 1;
                }

                if (paramType == 1) {
                    XWordbook wb = wbquery.query((String) item.get("mode"));

                    if (((Column) item.get("column")).getType().toString()
                             .equals(StringTool.VARCHAR.toString())) {
                        /*like*/
                        if (wb.getName().toLowerCase().equals("like")) {
                            sql.append(" AND " +
                                (((Column) item.get("column")).associateTable()
                                  .getName()) + "." +
                                ((Column) item.get("column")).getName() +
                                " LIKE '%" + cvalue + "%' ");
                        } else if (wb.getName().toLowerCase().equals("llike")) {
                            sql.append(" AND " +
                                (((Column) item.get("column")).associateTable()
                                  .getName()) + "." +
                                ((Column) item.get("column")).getName() +
                                " LIKE '%" + cvalue + "' ");
                        } else if (wb.getName().toLowerCase().equals("rlike")) {
                            sql.append(" AND " +
                                (((Column) item.get("column")).associateTable()
                                  .getName()) + "." +
                                ((Column) item.get("column")).getName() +
                                " LIKE '" + cvalue + "%' ");
                        } else {
                            sql.append(" AND " +
                                (((Column) item.get("column")).associateTable()
                                  .getName()) + "." +
                                ((Column) item.get("column")).getName() + " " +
                                wb.getName() + " '" + cvalue + "' ");
                        }
                    } else {
                        sql.append(" AND " +
                            (((Column) item.get("column")).associateTable()
                              .getName()) + "." +
                            ((Column) item.get("column")).getName() + " " +
                            wb.getName() + " " + cvalue + " ");
                    }
                } else if (paramType == 2) {
                    XWordbook wb = wbquery.query((String) item.get("mode"));

                    String[] params = cvalue.split(",");

                    sql.append(" AND (");

                    for (int k = 0; k < params.length; k++) {
                        if (((Column) item.get("column")).getType().toString()
                                 .equals(StringTool.VARCHAR.toString())) {
                            /*like*/
                            if (wb.getName().toLowerCase().equals("like")) {
                                sql.append((((Column) item.get("column")).associateTable()
                                             .getName()) + "." +
                                    ((Column) item.get("column")).getName() +
                                    " LIKE '%" + params[k] + "%' ");
                            } else if (wb.getName().toLowerCase().equals("llike")) {
                                sql.append((((Column) item.get("column")).associateTable()
                                             .getName()) + "." +
                                    ((Column) item.get("column")).getName() +
                                    " LIKE '%" + params[k] + "' ");
                            } else if (wb.getName().toLowerCase().equals("rlike")) {
                                sql.append((((Column) item.get("column")).associateTable()
                                             .getName()) + "." +
                                    ((Column) item.get("column")).getName() +
                                    " LIKE '" + params[k] + "%' ");
                            } else {
                                sql.append((((Column) item.get("column")).associateTable()
                                             .getName()) + "." +
                                    ((Column) item.get("column")).getName() +
                                    " " + wb.getName() + " '" + params[k] +
                                    "' ");
                            }
                        } else {
                            sql.append((((Column) item.get("column")).associateTable()
                                         .getName()) + "." +
                                ((Column) item.get("column")).getName() + " " +
                                wb.getName() + " " + params[k] + " ");
                        }

                        if (k != (params.length - 1)) {
                            sql.append(" OR ");
                        }
                    }

                    sql.append(") ");
                }
            }
        }

        List restraintlist = tq.irestraintItems();

        if ((restraintlist != null) && !restraintlist.isEmpty()) {
            for (int i = 0; i < restraintlist.size(); i++) {
                Map item = (Map) restraintlist.get(i);

                String defaultValue = StringTool.checkString(item.get("param"));

                if (defaultValue.startsWith("@")) {
                    defaultValue = StringTool.checkString(param.get(
                                defaultValue.substring(1)));
                }

                //param.put(item.icolumn().getName(), defaultValue);
                if (!defaultValue.equals("")) {
                    XWordbook wb = wbquery.query((String) item.get("mode"));

                    if (((Column) item.get("column")).getType().toString()
                             .equals(StringTool.VARCHAR.toString())) {
                        /*like*/
                        if (wb.getName().toLowerCase().equals("like")) {
                            sql.append(" AND " +
                                (((Column) item.get("column")).associateTable()
                                  .getName()) + "." +
                                ((Column) item.get("column")).getName() +
                                " LIKE '%" + defaultValue + "%' ");
                        } else if (wb.getName().toLowerCase().equals("llike")) {
                            sql.append(" AND " +
                                (((Column) item.get("column")).associateTable()
                                  .getName()) + "." +
                                ((Column) item.get("column")).getName() +
                                " LIKE '%" + defaultValue + "' ");
                        } else if (wb.getName().toLowerCase().equals("rlike")) {
                            sql.append(" AND " +
                                (((Column) item.get("column")).associateTable()
                                  .getName()) + "." +
                                ((Column) item.get("column")).getName() +
                                " LIKE '" + defaultValue + "%' ");
                        } else {
                            sql.append(" AND " +
                                (((Column) item.get("column")).associateTable()
                                  .getName()) + "." +
                                ((Column) item.get("column")).getName() + " " +
                                wb.getName() + " '" + defaultValue + "' ");
                        }
                    } else {
                        sql.append(" AND " +
                            (((Column) item.get("column")).associateTable()
                              .getName()) + "." +
                            ((Column) item.get("column")).getName() + " " +
                            wb.getName() + " " + defaultValue + " ");
                    }
                }
            }
        }

        if (primaryTable.hasPrimaryKey() && !tq.ipurview().equals("")) {
            sql.append(" AND " + primaryTable.getPrimaryKey()[0] +
                " = A.OBJ_ID ");
        }

        return sql.toString();
    }

    /**
     * 获得权限校验sql
     * @param user 会话
     * @param tq 表查询设置
     * @param table 表
     * @return String
     */
    private static String getPurviewSql(IUser user, TableQuery tq, Table table) {
        StringBuffer purviewSql = new StringBuffer(
                "SELECT DISTINCT(OBJ_ID) FROM ISYS_PURVIEW WHERE ");

        purviewSql.append(" PURVIEW IN (" + tq.ipurview() +
            ") AND OBJ_TABLE = '" + table.getName() + "' ");

        purviewSql.append(" AND (");

        List list = user.iroles();

        if ((list != null) && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                IRole role = (IRole) list.get(i);

                purviewSql.append(" (ROLE_TYPE = 'role' AND ROLE_ID = '" +
                    role.id() + "') OR ");
            }
        }

        String groupidstr = user.iallgroupflumeid(",");

        String[] groupids = groupidstr.split(",");

        if ((groupids != null) && (groupids.length > 0)) {
            for (int i = 0; i < groupids.length; i++) {
                purviewSql.append("(ROLE_TYPE = 'group' AND ROLE_ID = '" +
                    groupids[i] + "') OR ");
            }
        }

        purviewSql.append(" (ROLE_TYPE = 'user' AND ROLE_ID = '" + user.id() +
            "') ");

        purviewSql.append(")");

        /*if (tq.ipurviewRole().equals("@sessionUser")) {

        } else {
            String[] roles = tq.ipurviewRole().split(",");

            if ((roles != null) && (roles.length > 0)) {
                purviewSql.append("(");

                for (int i = 0; i < roles.length; i++) {
                    String role = roles[i];

                    if (role.trim().equals("")) {
                        role = "";
                    }

                    if (i < (roles.length - 1)) {
                        purviewSql.append(" ROLE_ID = '" + role + "' OR ");
                    } else {
                        purviewSql.append(" ROLE_ID = '" + role + "' ");
                    }
                }

                purviewSql.append(")");
            } else {
                purviewSql.append(" 1 = -1 ");
            }

            purviewSql.append(" AND PURVIEW IN (" + tq.ipurview() +
                ") AND OBJ_TABLE = '" + table.getName() + "' ");
        }*/
        return purviewSql.toString();
    }
}
